--region [dbo].[Adduserinfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[Adduserinfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[Adduserinfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[Adduserinfo]
GO

CREATE PROCEDURE [dbo].[Adduserinfo]
	@UserTypeid int,
	@UserTypeName nvarchar(50),
	@UserName nvarchar(50),
	@Email nvarchar(50),
	@Password nvarchar(32),
	@PasswordBak nvarchar(32),
	@ProvinceId int,
	@CityId int,
	@Address nvarchar(100),
	@ZipCode nvarchar(10),
	@Gender nvarchar(50),
	@Avatar nvarchar(256),
	@AvatarType int,
	@RegisteredDate datetime,
	@Privacy int,
	@Telephone nvarchar(50),
	@MobilePhone nvarchar(50),
	@Description nvarchar(max),
	@GroupIds nvarchar(200),
	@IsActive tinyint,
	@ActiveCode nvarchar(32),
	@IsOnline tinyint,
	@LastActivityDate datetime,
	@LastUpdateDate datetime,
	@LoginTime int,
	@Interests nvarchar(200),
	@ViewCount int,
	@Birthday datetime,
	@MSN nvarchar(100),
	@QQ nvarchar(50)

AS

INSERT INTO [userinfo] (
	[UserTypeid],
	[UserTypeName],
	[UserName],
	[Email],
	[Password],
	[PasswordBak],
	[ProvinceId],
	[CityId],
	[Address],
	[ZipCode],
	[Gender],
	[Avatar],
	[AvatarType],
	[RegisteredDate],
	[Privacy],
	[Telephone],
	[MobilePhone],
	[Description],
	[GroupIds],
	[IsActive],
	[ActiveCode],
	[IsOnline],
	[LastActivityDate],
	[LastUpdateDate],
	[LoginTime],
	[Interests],
	[ViewCount],
	[Birthday],
	[MSN],
	[QQ]
) VALUES (
	@UserTypeid,
	@UserTypeName,
	@UserName,
	@Email,
	@Password,
	@PasswordBak,
	@ProvinceId,
	@CityId,
	@Address,
	@ZipCode,
	@Gender,
	@Avatar,
	@AvatarType,
	@RegisteredDate,
	@Privacy,
	@Telephone,
	@MobilePhone,
	@Description,
	@GroupIds,
	@IsActive,
	@ActiveCode,
	@IsOnline,
	@LastActivityDate,
	@LastUpdateDate,
	@LoginTime,
	@Interests,
	@ViewCount,
	@Birthday,
	@MSN,
	@QQ
)

GO
--endregion


--region [dbo].[AddVisitRecord]
------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddVisitRecord]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddVisitRecord]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddVisitRecord]
GO

CREATE PROCEDURE [dbo].[AddVisitRecord]
	@HostUserId int,
	@VisitUserId int,
	@VisitUserName nvarchar(100),
	@VisitTime datetime

AS

INSERT INTO [VisitRecord] (
       [HostUserId],
       [VisitUserId],
       [VisitUserName],
       [VisitTime]

) VALUES (
        @HostUserId,
        @VisitUserId,
        @VisitUserName,
        @VisitTime
)

GO
--endregion

--region [dbo].[UpdateVisitRecord]
------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateVisitRecord]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateVisitRecord]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateVisitRecord]
GO

CREATE PROCEDURE [dbo].[UpdateVisitRecord]
    @Id int,
	@HostUserId int,
	@VisitUserId int,
	@VisitUserName nvarchar(100),
	@VisitTime datetime

AS

UPDATE [VisitRecord] SET
       [HostUserId] = @HostUserId,
       [VisitUserId] = @VisitUserId,
       [VisitUserName] = @VisitUserName,
       [VisitTime] = @VisitTime
WHERE
       [Id] = @Id
      
GO
--endregion

--region [dbo].[GetLattestVisitRecordList]
------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetLattestVisitRecordList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetLattestVisitRecordList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetLattestVisitRecordList]
GO

CREATE PROCEDURE [dbo].[GetLattestVisitRecordList]
	@N int,
	@WhereCondition nvarchar(512),
	@OrderByExpression nvarchar(250) = NULL
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
SELECT TOP ' + STR(@N) + '
	[Id],
	[HostUserId],
	[VisitUserId],
	[VisitUserName],
	[VisitTime]
FROM
	[VisitRecord]
WHERE'

    IF @WhereCondition <> ''
	BEGIN
	SET @SQL = @SQL + @WhereCondition
	END
	
	IF @OrderByExpression <> ''
	BEGIN
		SET @SQL = @SQL + '
	ORDER BY
		' + @OrderByExpression 
	END	
	
EXEC sp_executesql @SQL

GO
--endregion


--region [dbo].[UpdateUserViewCount]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateUserViewCount]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateUserViewCount]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateUserViewCount]
GO

CREATE PROCEDURE [dbo].[UpdateUserViewCount]
	@Id int
AS

UPDATE [userinfo] SET
	[ViewCount] = [ViewCount] + 1
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[UpdateUserProfile]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateUserProfile]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateUserProfile]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateUserProfile]
GO

CREATE PROCEDURE [dbo].[UpdateUserProfile]
	@Id int,
	@ProvinceId int,
	@CityId int,
	@Address nvarchar(100),
	@ZipCode nvarchar(10),
	@Birthday datetime,
	@Gender nvarchar(50),
	@Privacy int,
	@Telephone nvarchar(50),
	@MobilePhone nvarchar(50),
	@Description nvarchar(max),
	@GroupIds nvarchar(200),
	@Interests nvarchar(200),
	@ViewCount int,
	@MSN nvarchar(100),
	@QQ nvarchar(50)
AS

UPDATE [userinfo] SET
	[ProvinceId] = @ProvinceId,
	[CityId] = @CityId,
	[Address] = @Address,
	[ZipCode] = @ZipCode,
	[Birthday] = @Birthday,
	[Gender] = @Gender,
	[Privacy] = @Privacy,
	[Telephone] = @Telephone,
	[MobilePhone] = @MobilePhone,
	[Description] = @Description,
	[GroupIds] = @GroupIds,
	[Interests] = @Interests,
	[ViewCount] = @ViewCount,
	[MSN] = @MSN,
	[QQ] = @QQ
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[UpdateUserAvatar]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateUserAvatar]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateUserAvatar]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateUserAvatar]
GO

CREATE PROCEDURE [dbo].[UpdateUserAvatar]
	@Id int,
	@Avatar nvarchar(256),
	@AvatarType int
AS

UPDATE [userinfo] SET
	[Avatar] = @Avatar,
	[AvatarType] = @AvatarType
WHERE
	[Id] = @Id

GO
--endregion

--region [dbo].[UpdateUserLoginState]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateUserLoginState]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateUserLoginState]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateUserLoginState]
GO

CREATE PROCEDURE [dbo].[UpdateUserLoginState]
	@uid int,
	@state int
AS

UPDATE [userinfo] SET
	[IsOnline] = @state
WHERE
	[Id] = @uid

GO
--endregion

--region [dbo].[UpdateUserLoginTime]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateUserLoginTime]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateUserLoginTime]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateUserLoginTime]
GO

CREATE PROCEDURE [dbo].[UpdateUserLoginTime]
	@Id int
AS

UPDATE [userinfo] SET
	[LoginTime] = [LoginTime] + 1,
	[lastactivitydate] = getdate() 
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[UpdateUserPassword]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateUserPassword]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateUserPassword]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateUserPassword]
GO

CREATE PROCEDURE [dbo].[UpdateUserPassword]
	@Id int,
	@Password nvarchar(32)
AS

UPDATE [userinfo] SET
	[Password] = @Password,
	[PasswordBak] = @Password 
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[ResetUserPassword]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[ResetUserPassword]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[ResetUserPassword]') IS NOT NULL
	DROP PROCEDURE [dbo].[ResetUserPassword]
GO

CREATE PROCEDURE [dbo].[ResetUserPassword]
	@Id int,
	@Password nvarchar(32)
AS

UPDATE [userinfo] SET
	[PasswordBak] = @Password 
WHERE
	[Id] = @Id

GO
--endregion


--region [dbo].[ActiveUserByActiveCode]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[ActiveUserByActiveCode]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[ActiveUserByActiveCode]') IS NOT NULL
	DROP PROCEDURE [dbo].[ActiveUserByActiveCode]
GO

CREATE PROCEDURE [dbo].[ActiveUserByActiveCode]
	@UserName nvarchar(50),
	@ActiveCode nvarchar(32)
AS

UPDATE [userinfo] SET
	[IsActive] = 1
WHERE
	[UserName] = @UserName AND
	[ActiveCode] = @ActiveCode

GO
--endregion


--region [dbo].[Deluserinfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[Deluserinfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[Deluserinfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[Deluserinfo]
GO

CREATE PROCEDURE [dbo].[Deluserinfo]
	@WhereCondition nvarchar(512)
AS

DECLARE @SQL nvarchar(3250)

SET @SQL = '
DELETE FROM
	[userinfo]
WHERE
	' + @WhereCondition

EXEC sp_executesql @SQL

GO
--endregion

--region [dbo].[Getuserinfo]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[Getuserinfo]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[Getuserinfo]') IS NOT NULL
	DROP PROCEDURE [dbo].[Getuserinfo]
GO

CREATE PROCEDURE [dbo].[Getuserinfo]
	@condstr nvarchar(512)
AS
	DECLARE @strSQL VARCHAR(1024)

set @strSQL = '
SELECT
	[Id],
	[UserTypeid],
	[UserTypeName],
	[UserName],
	[Email],
	[Password],
	[PasswordBak],
	''Province'' = dbo.GetDistrictName([ProvinceId]),
	''City'' = dbo.GetDistrictName([CityId]),
	[ProvinceId],
	[CityId],
	[Address],
	[ZipCode],
	[Birthday],
	''Age'' = DATEDIFF(year, [UserInfo].[Birthday], getdate()),
	[Gender],
	[Avatar],
	[AvatarType],
	[RegisteredDate],
	[Privacy],
	[Telephone],
	[MobilePhone],
	[Description],
	[GroupIds],
	[IsActive],
	[ActiveCode],
	[IsOnline],
	[LastActivityDate],
	[LastUpdateDate],
	[LoginTime],
	[Interests],
	[ViewCount],
	[MSN],
	[QQ]
FROM
	[userinfo]
WHERE '
	+ @condstr

EXEC(@strSQL)

GO
--endregion


/**********************************************************************
GetUserListByType
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetUserListByType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetUserListByType
GO

create procedure GetUserListByType
    @pagesize int
   ,@pageindex int
   ,@usertypeid int
as
   DECLARE @strSQL VARCHAR(600)
   DECLARE @condstr varchar(32)

   set @condstr = ' [usertypeid] = ' + STR(@usertypeid) + ' '

   select count([id]) from [user] where [usertypeid] = @usertypeid
   exec(@strSQL) 
	
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
         ''Id'' = [id], 
         ''UserName'' = [username]
      from 
           [user] '
	if @pageindex > 1
		begin
		   set @strSQL = @strSQL + '
			 where [id] < 
			  ( 
				SELECT   min([id])  
				FROM ( 
				   SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [id] 
				   FROM [user] where ' + @condstr +
			 ' ORDER BY [id] DESC) AS tblTmp ) and ' + @condstr       
	   end
   else 
	   begin
			set @strSQL = @strSQL + ' where ' + @condstr
	   end

   set @strSQL = @strSQL  + 'ORDER BY [id] DESC'

   exec(@strSQL)
 
GO


/**********************************************************************
GetUserListByUserName
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetUserListByUserName') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetUserListByUserName
GO

create procedure GetUserListByUserName
   @pagesize int
   ,@pageindex int
   ,@usertypeid int
   ,@username nvarchar(64)
  
as
   DECLARE @strSQL VARCHAR(600)
   DECLARE @condstr varchar(96)

   set @condstr = ' [usertypeid] = ' + STR(@usertypeid) + ' and [username] like ''%' + @username + '%'' '

   set @strSQL = 'select count([id]) from [userinfo] where ' + @condstr
   exec(@strSQL) 
	
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
         ''Id'' = [id], 
         ''UserName'' = [username]
      from 
           [userinfo] '
	if @pageindex > 1
		begin
		   set @strSQL = @strSQL + '
			 where [id] < 
			  ( 
				SELECT   min([id])  
				FROM ( 
				   SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [id] 
				   FROM [userinfo] where ' + @condstr +
			 ' ORDER BY [id] DESC) AS tblTmp ) and ' + @condstr       
	   end
   else 
	   begin
			set @strSQL = @strSQL + ' where ' + @condstr
	   end

   set @strSQL = @strSQL  + 'ORDER BY [id] DESC'

   exec(@strSQL)
 
GO



/**********************************************************************
GetUserList
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetUserList') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetUserList
GO

create procedure GetUserList
   @pagesize int
   ,@pageindex int
   ,@condstr nvarchar(512)
  
as
   DECLARE @strSQL VARCHAR(1024)

   set @strSQL = 'select count([id]) from [userinfo] where ' + @condstr
   exec(@strSQL) 
	
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
         ''Id'' = [id], 
         ''UserName'' = [username]
      from 
           [userinfo] '
	if @pageindex > 1
		begin
		   set @strSQL = @strSQL + '
			 where [id] < 
			  ( 
				SELECT   min([id])  
				FROM ( 
				   SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [id] 
				   FROM [userinfo] where ' + @condstr +
			 ' ORDER BY [id] DESC) AS tblTmp ) and ' + @condstr       
	   end
   else 
	   begin
			set @strSQL = @strSQL + ' where ' + @condstr
	   end

   set @strSQL = @strSQL  + 'ORDER BY [id] DESC'

   exec(@strSQL)
 
GO



/**********************************************************************
GetUserListByDiseaseId
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetUserListByDiseaseId') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetUserListByDiseaseId
GO


create procedure GetUserListByDiseaseId
    @pagesize int
   ,@pageindex int
   ,@diseaseId int 
   ,@userType int 

as
   DECLARE @strSQL VARCHAR(1024)

   set @strSQL = 'select count([userinfo].[id]) from [userinfo] left join [patientrecord] 
      on [userinfo].[id] = [patientrecord].[patientid] where [patientrecord].[diseaseid] ='+STR(@diseaseId)       
  
   exec(@strSQL) 
	
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
         ''Id'' = [userinfo].[id], 
         ''UserName'' = [userinfo].[username]
         
      from 
           [userinfo] left join [patientrecord]
      on  
          [userinfo].[id] = [patientrecord].[patientid] where [patientrecord].[diseaseid] = '+STR(@diseaseId)

	if @pageindex > 1
		begin
		   set @strSQL = @strSQL + '
			 and  [id] < 
			  ( 
				SELECT   min([id])  
				FROM ( 
				   SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [id] 

				  from  [userinfo] left join [patientrecord]
      on  
          [userinfo].[id] = [patientrecord].[patientid] where [patientrecord].[diseaseid] = '+STR(@diseaseId)

			+ ' ORDER BY [userinfo].[id] DESC) AS tblTmp )'    
	   end
   else 
	   begin
		  set @strSQL = @strSQL  + 'ORDER BY [userinfo].[id] DESC'	
	   end

   exec(@strSQL)
 

GO


/**********************************************************************
GetUserListByTreatmentId
***********************************************************************/
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetUserListByTreatmentId') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetUserListByTreatmentId
GO


create procedure GetUserListByTreatmentId
    @pagesize int
   ,@pageindex int
   ,@treatmentId int 
   ,@userType int 

as
   DECLARE @strSQL VARCHAR(1024)

   set @strSQL = 'select count([userinfo].[id]) from [userinfo],[patientrecord],[patienttreatment]
       where [userinfo].[id] = [patientrecord].[patientid] and [patienttreatment].[prid] =[patientrecord].[id] and [patienttreatment].[treatmentid]='+STR(@treatmentId)       
  
   exec(@strSQL) 
	
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
         ''Id'' = [userinfo].[id], 
         ''UserName'' = [userinfo].[username]
      from [userinfo],[patientrecord],[patienttreatment]
       where [userinfo].[id] = [patientrecord].[patientid] and [patienttreatment].[prid] =[patientrecord].[id] and [patienttreatment].[treatmentid]='+STR(@treatmentId) 
	if @pageindex > 1
	   begin
		   set @strSQL = @strSQL + '
			 and  [id] < 
			  ( 
				SELECT   min([id])  
				FROM ( 
				   SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [id] 
        from [userinfo],[patientrecord],[patienttreatment]
       where [userinfo].[id] = [patientrecord].[patientid] and [patienttreatment].[prid] =[patientrecord].[id] and [patienttreatment].[treatmentid]='+STR(@treatmentId) 

			+ ' ORDER BY [userinfo].[id] DESC) AS tblTmp )'    
	   end
   else 
	   begin
		  set @strSQL = @strSQL  + 'ORDER BY [userinfo].[id] DESC'	
	   end

   exec(@strSQL)
 

GO


/**********************************************************************
GetUserListBySymptomId
***********************************************************************/


/*
if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'GetUserListBySymptomId') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure GetUserListBySymptomId
*/  
GO

/*
create procedure GetUserListBySymptomId
    @pagesize int
   ,@pageindex int
   ,@symptomIdint 
   ,@userType int 

as
   DECLARE @strSQL VARCHAR(1024)

   set @strSQL = 'select count([userinfo].[id]) from [userinfo],[patientrecord],[diseasesymptom]
       where [userinfo].[id] = [patientrecord].[patientid] and [diseasesymptom].[prid] =[patientrecord].[id] and [diseasesymptom].[symptomid]='+STR(@symptomId)       
  
   exec(@strSQL) 
	
   set @strSQL = '
      SELECT TOP ' + STR(@pagesize) + '
         ''Id'' = [userinfo].[id], 
         ''UserName'' = [userinfo].[username]
      from [userinfo],[patientrecord],[diseasesymptom]
       where [userinfo].[id] = [patientrecord].[patientid] and [diseasesymptom].[prid] =[patientrecord].[id] and [diseasesymptom].[symptomid]='+STR(@symptomId) 
	if @pageindex > 1
	   begin
		   set @strSQL = @strSQL + '
			 and  [id] < 
			  ( 
				SELECT   min([id])  
				FROM ( 
				   SELECT TOP ' + STR((@pageindex-1)*@pagesize) + ' [id] 
        from [userinfo],[patientrecord],[diseasesymptom]
       where [userinfo].[id] = [patientrecord].[patientid] and [diseasesymptom].[prid] =[patientrecord].[id] and [diseasesymptom].[symptomid]='+STR(@symptomId) 

			+ ' ORDER BY [userinfo].[id] DESC) AS tblTmp )'    
	   end
   else 
	   begin
		  set @strSQL = @strSQL  + 'ORDER BY [userinfo].[id] DESC'	
	   end

   exec(@strSQL)
 
*/

GO


/**********************************************************************************/
/**********************************************************************************/

if exists 
  (
      select * from dbo.sysobjects 
      where id = object_id(N'AddUserType') 
      and OBJECTPROPERTY(id, N'IsProcedure') = 1
  )
  drop procedure AddUserType
GO



create procedure AddUserType
   @usertypename nvarchar(50)
as

   insert into [usertype]
   ([usertypename]) values(@usertypename)

GO


--region [dbo].[GetUserIdByName]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetUserIdByName]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetUserIdByName]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetUserIdByName]
GO

CREATE PROCEDURE [dbo].[GetUserIdByName]
	@UserName nvarchar(50)
AS

SELECT
	[Id]
FROM
	[userinfo]
WHERE 
	[UserName] = @UserName

GO
--endregion


--region [dbo].[GetSystemEamilReveiverList]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSystemEamilReveiverList]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSystemEamilReceiverList]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSystemEamilReceiverList]
GO

CREATE PROCEDURE [dbo].[GetSystemEamilReceiverList]
AS
	SELECT
		[Id],
		[UserName],
		[Email]
	FROM
		[userinfo],[patientrecord]
	WHERE 
	    [userinfo].[Id] = [patientrecord].[patientid]
	AND
	    DATEDIFF(day, [patientrecord].[updatedate], getdate()) > 30 

GO
--endregion


--region [dbo].[GetSystemEmail]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[GetSystemEmail]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[GetSystemEmail]') IS NOT NULL
	DROP PROCEDURE [dbo].[GetSystemEmail]
GO

CREATE PROCEDURE [dbo].[GetSystemEmail]
    @ReceiverId int,
    @EmailType  int
AS

SELECT * FROM [SystemEmail] WHERE [ReceiverId] = @ReceiverId AND [EmailType] = @EmailType

GO
--endregion


--region [dbo].[AddSystemEmail]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[AddSystemEmail]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[AddSystemEmail]') IS NOT NULL
	DROP PROCEDURE [dbo].[AddSystemEmail]
GO

CREATE PROCEDURE [dbo].[AddSystemEmail]
    @ReceiverId int,
    @LatestSendTime datetime,
    @EmailType  int
AS

INSERT INTO [SystemEmail] (
	[ReceiverId],
	[LatestSendTime],
	[EmailType]
	)
Values (
    @ReceiverId,
    @LatestSendTime,
    @EmailType
    )
GO
--endregion

--region [dbo].[UpdateSystemEmail]

------------------------------------------------------------------------------------------------------------------------
-- Template:       StoredProcedres.cst
-- Procedure Name: [dbo].[UpdateSystemEmail]
------------------------------------------------------------------------------------------------------------------------

IF OBJECT_ID(N'[dbo].[UpdateSystemEmail]') IS NOT NULL
	DROP PROCEDURE [dbo].[UpdateSystemEmail]
GO

CREATE PROCEDURE [dbo].[UpdateSystemEmail]
    @Id int,
    @ReceiverId int,
    @LatestSendTime datetime,
    @EmailType  int
AS

UPDATE [SystemEmail] SET
	[ReceiverId] = @ReceiverId,
	[LatestSendTime] = @LatestSendTime,
	[EmailType] = @EmailType
WHERE
	[Id] = @Id

GO
--endregion

